using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_DOWNLOAD : DALConnection
    {
        #region Incluir

        public void incluir(T_DOWNLOAD obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                obj.TX_NOME_ARQUIVO = obj.TX_NOME_ARQUIVO.Replace("\\", "#");
                string[] arquivo = obj.TX_NOME_ARQUIVO.Split('#');

                SQL.Append(" INSERT INTO T_DOWNLOAD ");
                SQL.Append("    (TX_OBRA, TX_NOME_ARQUIVO, VL_TAMANHO, TX_REFORMADOR, TX_ANO, TX_MES) ");
                SQL.Append(" VALUES ");
                SQL.Append("    ('" + arquivo[arquivo.Length-1] + "', '" + obj.TX_NOME_ARQUIVO + "', " + obj.VL_TAMANHO.ToString() + ",'" + obj.TX_REFORMADOR + "','" + obj.TX_ANO +"','" + obj.TX_MES + "') ");

                DbCommand insertT_DOWNLOAD = database.GetSqlStringCommand(SQL.ToString());

                insertT_DOWNLOAD.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_DOWNLOAD, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Incluir[]

        public void incluir(T_DOWNLOAD[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] insertT_DOWNLOAD = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_DOWNLOAD o = obj[index];
                    SQL = new StringBuilder();

                    SQL.Append(" INSERT INTO T_DOWNLOAD ");
                    SQL.Append("    (ID_DOWNLOAD, ID_AUTOR, TX_OBRA, TX_NOME_ARQUIVO, VL_TAMANHO, FL_TIPO) ");
                    SQL.Append(" VALUES ");
                    SQL.Append("    (:ID_DOWNLOAD, :ID_AUTOR, :TX_OBRA, :TX_NOME_ARQUIVO, :VL_TAMANHO, :FL_TIPO) ");

                    insertT_DOWNLOAD[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(insertT_DOWNLOAD[index], ":ID_DOWNLOAD", DbType.Int64, o.ID_DOWNLOAD);
                    database.AddInParameter(insertT_DOWNLOAD[index], ":TX_OBRA", DbType.String, o.TX_OBRA);
                    database.AddInParameter(insertT_DOWNLOAD[index], ":TX_NOME_ARQUIVO", DbType.String, o.TX_NOME_ARQUIVO);
                    database.AddInParameter(insertT_DOWNLOAD[index], ":VL_TAMANHO", DbType.Int32, o.VL_TAMANHO);
                    database.AddInParameter(insertT_DOWNLOAD[index], ":FL_TIPO", DbType.String, o.FL_TIPO);

                    insertT_DOWNLOAD[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in insertT_DOWNLOAD)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_DOWNLOAD obj)
        {
            StringBuilder SQL = new StringBuilder();
            StringBuilder SQL2 = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_DOWNLOAD SET ");
                SQL.Append("    TX_OBRA = '" + obj.TX_OBRA + "', ");
                SQL.Append("    FL_TIPO = '" + obj.FL_TIPO + "', ");
                SQL.Append("    ID_AUTOR_ENC = " + obj.ID_AUTOR_ENC.ToString() + ", ");
                SQL.Append("    ID_AUTOR_DES = " + obj.ID_AUTOR_DES.ToString() + ", ");
                SQL.Append("    TX_REFORMADOR = '" + obj.TX_REFORMADOR + "', ");
                SQL.Append("    TX_ANO = '" + obj.TX_ANO + "', ");
                SQL.Append("    TX_MES = '" + obj.TX_MES + "' ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_DOWNLOAD = " + obj.ID_DOWNLOAD.ToString());

                DbCommand updateT_DOWNLOAD = database.GetSqlStringCommand(SQL.ToString());

                updateT_DOWNLOAD.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_DOWNLOAD, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar[]

        public void alterar(T_DOWNLOAD[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] updateT_DOWNLOAD = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_DOWNLOAD o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" UPDATE T_DOWNLOAD SET ");
                    SQL.Append("    ID_AUTOR = :ID_AUTOR , ");
                    SQL.Append("    TX_OBRA = :TX_OBRA , ");
                    SQL.Append("    TX_NOME_ARQUIVO = :TX_NOME_ARQUIVO , ");
                    SQL.Append("    VL_TAMANHO = :VL_TAMANHO , ");
                    SQL.Append("    FL_TIPO = :FL_TIPO ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_DOWNLOAD = :ID_DOWNLOAD  ");

                    updateT_DOWNLOAD[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(updateT_DOWNLOAD[index], ":ID_DOWNLOAD", DbType.Int64, o.ID_DOWNLOAD);
                    database.AddInParameter(updateT_DOWNLOAD[index], ":TX_OBRA", DbType.String, o.TX_OBRA);
                    database.AddInParameter(updateT_DOWNLOAD[index], ":TX_NOME_ARQUIVO", DbType.String, o.TX_NOME_ARQUIVO);
                    database.AddInParameter(updateT_DOWNLOAD[index], ":VL_TAMANHO", DbType.Int32, o.VL_TAMANHO);
                    database.AddInParameter(updateT_DOWNLOAD[index], ":FL_TIPO", DbType.String, o.FL_TIPO);

                    updateT_DOWNLOAD[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in updateT_DOWNLOAD)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_DOWNLOAD obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_DOWNLOAD ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_DOWNLOAD = " + obj.ID_DOWNLOAD.ToString());

                DbCommand deleteT_DOWNLOAD = database.GetSqlStringCommand(SQL.ToString());

                deleteT_DOWNLOAD.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_DOWNLOAD, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir[]

        public void excluir(T_DOWNLOAD[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] deleteT_DOWNLOAD = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_DOWNLOAD o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" DELETE FROM T_DOWNLOAD ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_DOWNLOAD = :ID_DOWNLOAD  ");

                    deleteT_DOWNLOAD[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(deleteT_DOWNLOAD[index], ":ID_DOWNLOAD", DbType.Int64, o.ID_DOWNLOAD);

                    deleteT_DOWNLOAD[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in deleteT_DOWNLOAD)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_DOWNLOAD obterPorPK(long pID_DOWNLOAD)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_DOWNLOAD ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_DOWNLOAD = " + pID_DOWNLOAD.ToString());

                DbCommand consultaT_DOWNLOAD = database.GetSqlStringCommand(SQL.ToString());
                consultaT_DOWNLOAD.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_DOWNLOAD, DALConnection.transaction);
                T_DOWNLOAD obj = null;

                if (reader.Read())
                {
                    obj = new T_DOWNLOAD();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_DOWNLOAD")))
                    {
                        obj.ID_DOWNLOAD = Int64.Parse(reader["ID_DOWNLOAD"].ToString());
                    }                    
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_OBRA")))
                    {
                        obj.TX_OBRA = reader["TX_OBRA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME_ARQUIVO")))
                    {
                        obj.TX_NOME_ARQUIVO = reader["TX_NOME_ARQUIVO"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("VL_TAMANHO")))
                    {
                        obj.VL_TAMANHO = Int32.Parse(reader["VL_TAMANHO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_TIPO")))
                    {
                        obj.FL_TIPO = reader["FL_TIPO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REFORMADOR")))
                    {
                        obj.TX_REFORMADOR = reader["TX_REFORMADOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_ANO")))
                    {
                        obj.TX_ANO = reader["TX_ANO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_MES")))
                    {
                        obj.TX_MES = reader["TX_MES"].ToString();
                    }
                }
                reader.Dispose();
                consultaT_DOWNLOAD.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ListarTodos

        public List<T_DOWNLOAD> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_DOWNLOAD ORDER BY TX_OBRA");

                DbCommand consultaT_DOWNLOAD = database.GetSqlStringCommand(SQL.ToString());
                consultaT_DOWNLOAD.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_DOWNLOAD, DALConnection.transaction);
                List<T_DOWNLOAD> lista = new List<T_DOWNLOAD>();
                T_DOWNLOAD obj = null;

                while (reader.Read())
                {
                    obj = new T_DOWNLOAD();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_DOWNLOAD")))
                    {
                        obj.ID_DOWNLOAD = Int64.Parse(reader["ID_DOWNLOAD"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_OBRA")))
                    {
                        obj.TX_OBRA = reader["TX_OBRA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME_ARQUIVO")))
                    {
                        obj.TX_NOME_ARQUIVO = reader["TX_NOME_ARQUIVO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("VL_TAMANHO")))
                    {
                        obj.VL_TAMANHO = Int32.Parse(reader["VL_TAMANHO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR_ENC")))
                    {
                        obj.ID_AUTOR_ENC = Int32.Parse(reader["ID_AUTOR_ENC"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR_DES")))
                    {
                        obj.ID_AUTOR_DES = Int32.Parse(reader["ID_AUTOR_DES"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_TIPO")))
                    {
                        obj.FL_TIPO = reader["FL_TIPO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REFORMADOR")))
                    {
                        obj.TX_REFORMADOR = reader["TX_REFORMADOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_ANO")))
                    {
                        obj.TX_ANO = reader["TX_ANO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_MES")))
                    {
                        obj.TX_MES = reader["TX_MES"].ToString();
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_DOWNLOAD.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<VOT_DOWNLOAD> consultar(string obra, string tipo, int autor_enc, int autor_des, string reformador, string ano, string mes)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append("SELECT D.ID_DOWNLOAD AS ID_DOWNLOAD, D.TX_OBRA AS TX_OBRA, D.TX_NOME_ARQUIVO AS TX_NOME_ARQUIVO, D.ID_AUTOR_ENC AS ID_AUTOR_ENC, ");
                SQL.Append("D.ID_AUTOR_DES AS ID_AUTOR_DES, D.VL_TAMANHO AS VL_TAMANHO, D.FL_TIPO AS FL_TIPO, D.TX_REFORMADOR, D.TX_ANO, D.TX_MES ");
                SQL.Append("FROM T_DOWNLOAD D WHERE ");



                if (!ano.Equals("") || !mes.Equals(""))
                    SQL.Append(" D.TX_REFORMADOR IS NOT NULL AND D.TX_REFORMADOR like '" + reformador+  "%'");                
                else
                    SQL.Append(" D.TX_REFORMADOR = '' AND D.TX_OBRA LIKE '" + obra + "%'");                

                if (!ano.Equals(""))
                    SQL.Append(" AND D.TX_ANO = " + ano);

                if (!mes.Equals(""))
                    SQL.Append(" AND D.TX_MES = '" + mes + "'");

                
                if (!tipo.Equals("") && !tipo.Equals("0"))
                    SQL.Append(" AND D.FL_TIPO LIKE '" + tipo + "%'");

                if (!autor_enc.Equals(0))
                    SQL.Append(" AND D.ID_AUTOR_ENC = " + autor_enc);

                if (!autor_des.Equals(0))
                    SQL.Append(" AND D.ID_AUTOR_DES = " + autor_des);

                SQL.Append(" ORDER BY D.TX_OBRA");

                DbCommand consultaT_DOWNLOAD = database.GetSqlStringCommand(SQL.ToString());
                consultaT_DOWNLOAD.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_DOWNLOAD, DALConnection.transaction);
                List<VOT_DOWNLOAD> lista = new List<VOT_DOWNLOAD>();
                VOT_DOWNLOAD obj = null;

                while (reader.Read())
                {
                    obj = new VOT_DOWNLOAD();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_DOWNLOAD")))
                    {
                        obj.ID_DOWNLOAD = Int64.Parse(reader["ID_DOWNLOAD"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_OBRA")))
                    {
                        obj.TX_OBRA = reader["TX_OBRA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_NOME_ARQUIVO")))
                    {
                        obj.TX_NOME_ARQUIVO = reader["TX_NOME_ARQUIVO"].ToString().Replace("#", "\\");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("VL_TAMANHO")))
                    {
                        obj.VL_TAMANHO = Int32.Parse(reader["VL_TAMANHO"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("FL_TIPO")))
                    {
                        obj.FL_TIPO = reader["FL_TIPO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR_ENC")))
                    {
                        obj.ID_AUTOR_ENCARNADO = Int32.Parse(reader["ID_AUTOR_ENC"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_AUTOR_DES")))
                    {
                        obj.ID_AUTOR_DESENCARNADO = Int32.Parse(reader["ID_AUTOR_DES"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_REFORMADOR")))
                    {
                        obj.TX_REFORMADOR = reader["TX_REFORMADOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_ANO")))
                    {
                        obj.TX_ANO = reader["TX_ANO"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_MES")))
                    {
                        obj.TX_MES = reader["TX_MES"].ToString();
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_DOWNLOAD.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}

